/**
 * 标签数据模型
 * 负责标签相关的数据库操作
 */

import { dbConnection } from '../connection'
import { Tag } from '../../../renderer/types'

/**
 * 标签模型类
 */
export class TagModel {
  /**
   * 创建新标签
   * @param tag 标签数据
   * @returns 创建的标签ID
   */
  static create(tag: Omit<Tag, 'id'>): number {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare(`
      INSERT INTO tags (name, color, created_at)
      VALUES (?, ?, ?)
    `)
    
    const result = stmt.run(
      tag.name,
      tag.color,
      new Date().toISOString()
    )
    
    return result.lastInsertRowid as number
  }

  /**
   * 根据ID获取标签
   * @param id 标签ID
   * @returns 标签数据
   */
  static getById(id: number): Tag | undefined {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT * FROM tags WHERE id = ?')
    return stmt.get(id) as Tag | undefined
  }

  /**
   * 根据名称获取标签
   * @param name 标签名称
   * @returns 标签数据
   */
  static getByName(name: string): Tag | undefined {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT * FROM tags WHERE name = ?')
    return stmt.get(name) as Tag | undefined
  }

  /**
   * 获取所有标签
   * @returns 标签列表
   */
  static getAll(): Tag[] {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT * FROM tags ORDER BY name')
    return stmt.all() as Tag[]
  }

  /**
   * 更新标签信息
   * @param id 标签ID
   * @param updates 更新数据
   * @returns 是否更新成功
   */
  static update(id: number, updates: Partial<Tag>): boolean {
    const db = dbConnection.getConnection()
    
    const fields = Object.keys(updates).filter(key => key !== 'id')
    if (fields.length === 0) return false
    
    const setClause = fields.map(field => `${field} = ?`).join(', ')
    const values = fields.map(field => (updates as any)[field])
    
    const stmt = db.prepare(`
      UPDATE tags 
      SET ${setClause}
      WHERE id = ?
    `)
    
    const result = stmt.run(...values, id)
    return result.changes > 0
  }

  /**
   * 删除标签
   * @param id 标签ID
   * @returns 是否删除成功
   */
  static delete(id: number): boolean {
    const db = dbConnection.getConnection()
    
    // 使用事务确保数据一致性
    const transaction = db.transaction(() => {
      // 删除标签与照片的关联
      const deletePhotosStmt = db.prepare('DELETE FROM photo_tags WHERE tag_id = ?')
      deletePhotosStmt.run(id)
      
      // 删除标签
      const deleteTagStmt = db.prepare('DELETE FROM tags WHERE id = ?')
      const result = deleteTagStmt.run(id)
      
      return result.changes > 0
    })
    
    return transaction()
  }

  /**
   * 向照片添加标签
   * @param photoId 照片ID
   * @param tagId 标签ID
   * @returns 是否添加成功
   */
  static addToPhoto(photoId: number, tagId: number): boolean {
    const db = dbConnection.getConnection()
    
    // 检查是否已存在
    const checkStmt = db.prepare('SELECT COUNT(*) as count FROM photo_tags WHERE photo_id = ? AND tag_id = ?')
    const { count } = checkStmt.get(photoId, tagId) as { count: number }
    
    if (count > 0) return false
    
    const stmt = db.prepare(`
      INSERT INTO photo_tags (photo_id, tag_id)
      VALUES (?, ?)
    `)
    
    const result = stmt.run(photoId, tagId)
    return result.changes > 0
  }

  /**
   * 从照片移除标签
   * @param photoId 照片ID
   * @param tagId 标签ID
   * @returns 是否移除成功
   */
  static removeFromPhoto(photoId: number, tagId: number): boolean {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('DELETE FROM photo_tags WHERE photo_id = ? AND tag_id = ?')
    const result = stmt.run(photoId, tagId)
    return result.changes > 0
  }

  /**
   * 获取照片的所有标签
   * @param photoId 照片ID
   * @returns 标签列表
   */
  static getPhotoTags(photoId: number): Tag[] {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare(`
      SELECT t.* FROM tags t
      INNER JOIN photo_tags pt ON t.id = pt.tag_id
      WHERE pt.photo_id = ?
      ORDER BY t.name
    `)
    
    return stmt.all(photoId) as Tag[]
  }

  /**
   * 获取标签的照片数量
   * @param tagId 标签ID
   * @returns 照片数量
   */
  static getPhotoCount(tagId: number): number {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT COUNT(*) as count FROM photo_tags WHERE tag_id = ?')
    const { count } = stmt.get(tagId) as { count: number }
    
    return count
  }

  /**
   * 获取热门标签
   * @param limit 限制数量
   * @returns 热门标签列表
   */
  static getPopularTags(limit: number = 10): Array<{ id: number; name: string; color: string; count: number }> {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare(`
      SELECT t.id, t.name, t.color, COUNT(pt.photo_id) as count
      FROM tags t
      INNER JOIN photo_tags pt ON t.id = pt.tag_id
      GROUP BY t.id, t.name, t.color
      ORDER BY count DESC
      LIMIT ?
    `)
    
    return stmt.all(limit) as Array<{ id: number; name: string; color: string; count: number }>
  }

  /**
   * 获取标签数量
   * @returns 标签总数
   */
  static getCount(): number {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT COUNT(*) as total FROM tags')
    const { total } = stmt.get() as { total: number }
    
    return total
  }
}